create external table jms_tmp.tmp_forecast_network_deliver_volume_route_dt (
   first_network_code string comment '始发机构编码',
   first_network_name string comment '始发机构名称',
   last_network_code string comment '目的机构编码',
   last_network_name string comment '目的机构名称',
   planned_departure_time string comment '规划发车时间',
   planned_arrival_time string comment '规划到车时间',
   total_days string comment 't+时效'
   update_time timstamp comment'更新时间'
) COMMENT '预测网点派件货量'
PARTITIONED BY (dt STRING COMMENT '日期分区 yyyy-MM-dd')
STORED AS PARQUET
LOCATION '/dw/hive/jms_tmp.db/external/tmp_forecast_network_deliver_volume_route_dt'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);


CREATE EXTERNAL TABLE jms_tmp.tmp_forecast_network_deliver_volume_detail_mid_hi(
  waybill_no string COMMENT '运单号',
  send_time timestamp COMMENT '发件时间',
  send_site_code string COMMENT '发件网点code',
  send_site_name string COMMENT '发件网点name',
  dispatch_network_code string COMMENT '派件网点code',
  dispatch_network_name string COMMENT '派件网点name',
  first_code string COMMENT '一段码',
  second_code string COMMENT '二段码',
  dest_center_code string COMMENT '目的中心code',
  dest_center_name string COMMENT '目的中心name',
  planned_departure_time string COMMENT '规划发车时间',
  planned_arrival_time string COMMENT '规划到件时间',
  total_days int COMMENT '时效天',
  dest_arrival_time timestamp COMMENT '目的网点规划到件时间',
  date_time string COMMENT '')
COMMENT '网点派件量班次货量'
PARTITIONED BY (dt string COMMENT '时间分区 yyyy-MM-dd-HH')
STORED AS PARQUET
LOCATION '/dw/hive/jms_tmp.db/external/tmp_forecast_network_deliver_volume_detail_mid_hi'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);
CREATE EXTERNAL TABLE jms_dm.dm_forecast_network_deliver_volume_detail_dt(
  waybill_no string COMMENT '运单号',
  send_time timestamp COMMENT '最新发件时间',
  send_site_code string COMMENT '最新发件网点code',
  send_site_name string COMMENT '最新发件网点name',
  first_code string COMMENT '一段码',
  second_code string COMMENT '二段码',
  forecast_center_code string COMMENT '预测目的中心code',
  forecast_center_name string COMMENT '预测目的中心name',
  forecast_network_code string COMMENT '预测目的网点code',
  forecast_network_name string COMMENT '预测目的网点name',
  forecast_arrival_time timestamp COMMENT '预测目的网点到件时间',
  forecast_shift_code string COMMENT '预测派件班次code',
  forecast_shifts string COMMENT '预测派件班次12',
  forecast_shift_name string COMMENT '预测派件班次name',
  actual_network_code string COMMENT '实际目的网点code',
  actual_network_name string COMMENT '实际目的网点name',
  actual_arrival_time timestamp COMMENT '实际目的网点到件时间',
  actual_shift_code string COMMENT '实际派件班次code',
  actual_shifts string COMMENT '实际派件班次12',
  actual_shift_name string COMMENT '实际派件班次name',
  date_time date COMMENT '时间查询',
  update_time timestamp COMMENT '更新时间',
  forecast_shift_time string COMMENT '预测班次时间',
  actual_shift_time string COMMENT '实际班次时间')
COMMENT '派件单量预测+实际单号到件'
PARTITIONED BY (dt string COMMENT '日期分区 yyyy-MM-dd')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://yl-bg-hdp/dw/hive/jms_dm.db/external/dm_forecast_network_deliver_volume_detail_dt'

-------------------
CREATE EXTERNAL TABLE jms_dm.dm_forecast_network_deliver_volume_cnt_hi(
  agent_code string COMMENT '代理区code',
  agent_name string COMMENT '代理区name',
  dispatch_network_code string COMMENT '目的网点code',
  dispatch_network_name string COMMENT '目的网点name',
  deliver_shift string COMMENT '派件班次时间',
  waybill_cnt int COMMENT '预计货量',
  deliver_shift_up string COMMENT '昨日派件班次时间',
  waybill_cnt_up int COMMENT '昨日实际派件量',
  date_time date COMMENT '规划到件日期',
  update_time timestamp COMMENT '更新时间')
COMMENT '派件单量预测汇总'
PARTITIONED BY (
  dt string COMMENT '时间分区 yyyy-MM-dd-HH')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://yl-bg-hdp/dw/hive/jms_dm.db/external/dm_forecast_network_deliver_volume_cnt_hi'

-------------------------------
alter table jms_dm.dm_forecast_network_deliver_volume_cnt_dt add COLUMNS (
deliver_shift_num int comment'预测派件班次 1,2,3'
,deliver_shift_num_up int comment'实际派件散货班次 1,2,3'
)CASCADE;
--doris
alter table jms_dm.dm_forecast_network_deliver_volume_cnt_dt add COLUMN (
deliver_shift_num int comment'预测派件班次 1,2,3'
,deliver_shift_num_up int comment'实际派件散货班次 1,2,3'
)